package com.example.demo.utils;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.junit.Test;


/**
 * 需要在pom.xml添加的jar包用于Eexcel导入导出等等
 * ----------------------------------------------
 <!-- (支持excel2003)Apache POI是一个纯Java编写用来操作Microsoft Office的框架，最常见的应用是让服务器后台按照特定的数据生成Excel表格提供给用户实用。 -->
 <dependency>
 <groupId>org.apache.poi</groupId>
 <artifactId>poi</artifactId>
 <version>4.0.0</version>
 </dependency>
 <!--对excel2007支持  -->
 <dependency>
 <groupId>org.apache.poi</groupId>
 <artifactId>poi-ooxml</artifactId>
 <version>4.0.0</version>
 </dependency>
 * ----------------------------------------------
 */



/**
 * Util提供的所有静态方法返回的对象都是Workbook,最后一步根据需求再做处理
 * @author learnhow
 */
public class ExeclUtil {
    public static final int Excel2003 = 0;
    public static final int Excel2007 = 1;
    /**
     * 根据版本号，获取Excel poi对象
     */
    public static Workbook getWorkbook(int edition,InputStream in)throws IOException {
        //判断Excel版本号，根据不同的版本号获取获得不同的Excel poi对象
        if(edition ==0 ) {
            return new HSSFWorkbook(in);
        }else if(edition == 1) {
            return new XSSFWorkbook(in);
        }
        return null;
    }
    /**
     * 从指定excel表格中逐行读取数据
     */
    public static List<List<String>> getExcelString(Workbook workbook ,int startRow,int startCol,int indexSheet){
        List<List<String>> stringTable=new ArrayList<List<String>>();
        //获取指定表对象
        Sheet sheet = workbook.getSheetAt(indexSheet);
        //获取最大行数
        int rowNum=sheet.getLastRowNum();
        for(int i=startRow;i<=rowNum;i++) {
            List<String> oneRow=new ArrayList<String>();
            Row row=sheet.getRow(i);
            //根据当前指针所在行数计算最大列数
            int colNum=row.getLastCellNum();
            for(int j=startCol;j<=colNum;j++) {
                //确定当前单元格
                Cell cell=row.getCell(j);
                String cellValue=null;
                if(cell!=null) {
                    //验证每一个单元格的类型
                    switch (cell.getCellType()) {
                        case NUMERIC:
                            //表格中返回的数字类型是科学计数法因此不能直接转换成字符串格式
                            cellValue=new BigDecimal(cell.getNumericCellValue()).toPlainString();
                            break;
                        case STRING:
                            cellValue=cell.getStringCellValue();
                            break;
                        case FORMULA:
                            cellValue=new BigDecimal(cell.getNumericCellValue()).toPlainString();
                            break;
                        case BLANK:
                            cellValue="";
                            break;
                        case BOOLEAN:
                            cellValue=Boolean.toString(cell.getBooleanCellValue());
                            break;
                        case ERROR:
                            cellValue="ERROR";
                            break;
                        default:
                            cellValue="UNDEFINE";
                    }
                }else {
                    cellValue="";
                }
                //生成一行数据
                oneRow.add(cellValue);
            }
            stringTable.add(oneRow);
        }
        return stringTable;
    }
    /**
     * 根据给定的数据直接生成workbook
     */
    public static Workbook createExcel(Workbook workbook,String sheetName,List<List<String>> data) {
        //临时测试用
        Sheet sheet;
        if(workbook.getSheetName(0)!=null){
            sheet=workbook.getSheet(workbook.getSheetName(0));
        }else {
            sheet = workbook.createSheet(sheetName);
        }
        Row row;
        Cell cell;
//        Cell cell=row.createCell(0);
        for(int i=0;i<data.size();i++) {
            List<String> oneRow=data.get(i);
            if(sheet.getRow(i)==null) {
                row = sheet.createRow(i);
            }else {
                row = sheet.getRow(i);
            }

            for (int j = 0; j < oneRow.size(); j++) {
                cell=row.createCell(j);
                cell.setCellValue(oneRow.get(j));
            }
        }
        return workbook;
    }


    /**
     * 往指定的sheet表中插入数据，插入的方法是提供一组valueMap.int[]是2维数组代表需要插入的数据坐标，从0开始
     */
    public static Workbook insertExcel(Workbook workbook,int sheetIndex,Map<int[],String> valueMap) {
        //获取表格
        Sheet sheet=workbook.getSheetAt(sheetIndex);
        /**
         * Iterator<Entry<String,String>> iter=map.entrySet().iterator();是什么意思获得map的迭代器，
         * 用作遍历map中的每一个键值对Iterator是迭代器，map之前应该定义过，姑且认为是HashMap。<Entry<String,String>>表示map中的键值对都是String类型的。
         * map.entrySet()是把HashMap类型的数据转换成集合类型map.entrySet().iterator()是去获得这个集合的迭代器，保存在iter里面。。迭代器这么用：
         * while(iter.hasNext()) {Entry obj = it.next();//就能获得map中的每一个键值对了
         */
        Iterator<Entry<int[],String>> it=valueMap.entrySet().iterator();
        while(it.hasNext()) {
            Entry<int[],String> cellEntry=it.next();
            int x=cellEntry.getKey()[0];
            int y=cellEntry.getKey()[1];
            String value=cellEntry.getValue();
            Row row=sheet.getRow(y);
            Cell cell=row.getCell(x);
            cell.setCellValue(value);
        }
        return workbook;
    }
    /**
     * 设置指定行的行高
     */
    public static Workbook setRowHeight(Workbook workbook,int rowHight,int sheetIndex,int rowIndex) {
        Sheet sheet=workbook.getSheetAt(sheetIndex);
        Row row=sheet.getRow(rowIndex);
        row.setHeight((short)rowHight);
        return workbook;
    }
    /**
     * 设置列宽
     */
    public static Workbook setColumnWidth(Workbook workbook,int columnWidth,int sheetIndex,int columnIndex) {
        Sheet sheet=workbook.getSheetAt(sheetIndex);
        sheet.setColumnWidth(columnIndex,columnWidth);
        return workbook;
    }
    /**
     * 删除指定行
     */
    public static Workbook removeRow(Workbook workbook,int sheetIndex,int rowIndex) {
        Sheet sheet=workbook.getSheetAt(sheetIndex);
        int lastRowNum=sheet.getLastRowNum();
        if(rowIndex >=0 && rowIndex <lastRowNum) {
            sheet.shiftRows(rowIndex+1,lastRowNum,-1);
        }
        if(rowIndex == lastRowNum) {
            sheet.removeRow(sheet.getRow(rowIndex));
        }
        return workbook;
    }
    /**
     * 在指定位置插入空白行
     */
    public static Workbook insertBlankRow(Workbook workbook,int sheetIndex,int rowIndex) {
        Sheet sheet=workbook.getSheetAt(sheetIndex);
        int lastRowNum=sheet.getLastRowNum();
        if(rowIndex>=0 && rowIndex<=lastRowNum) {
            sheet.shiftRows(rowIndex, lastRowNum, 1);
            //获得上一行的Row对象
            Row preRow=sheet.getRow(rowIndex-1);
            short rowNum=preRow.getLastCellNum();
            Row curRow=sheet.createRow(rowIndex);
            //新生成的Row创建与上一个行相同风格的Cell
            for(short i=preRow.getFirstCellNum();i<rowNum;i++) {
                Cell cell=preRow.getCell(i);
                CellStyle style=cell.getCellStyle();
                curRow.createCell(i).setCellStyle(style);
            }
            return workbook;
        }
        return null;
    }
    /**
     * 根据sheet(0)作为模版重建workbook
     */
    //String...sheetNames(可变参数，可传入0到n个参数)
    public static Workbook rebuildWorkbook(Workbook workbook,int sheetNum,String...sheetNames) {
        if(sheetNames.length==sheetNum) {
            for(int i=0;i<sheetNum;i++) {
                workbook.cloneSheet(0);
                //生成后面的工作表并指定表名
                workbook.setSheetName(i+1,sheetNames[i]);
            }
            //删除第一张工作表
            workbook.removeSheetAt(0);
            return workbook;
        }
        return null;
    }

    /**
     * 测试类，测试Execl工具类是否可用
     * @throws IOException
     */

    /**
     * 复制当前表，然后新增工作表
     * @throws IOException
     */
    @Test
    public void test() throws IOException {
        String filepath="C:\\a.xls";
        FileInputStream fs=new FileInputStream(filepath);
        Workbook workbook = getWorkbook(Excel2003, fs);

        List<String> list=new ArrayList<String>();
        list.add("a");
        list.add("a");
        list.add("a");

        List<List<String>> lt=new ArrayList<List<String>>();
        lt.add(list);
        lt.add(list);
        lt.add(list);

        String sheetName="工作表";
//        Workbook wk=new HSSFWorkbook();
        Workbook wk=createExcel(workbook,sheetName,lt);
//        Map<int[],String> valueMap1 = new HashMap<int[], String>();
//        int[]a= {0,0};
//        valueMap1.put(a, "abcdefg");
//        Workbook wk=insertExcel(workbook,0,valueMap1);
        String file="C:\\c.xls";
        FileOutputStream out=new FileOutputStream(file);
        wk.write(out);
        out.flush();

    }

    /**
     * 原表基础修改
     */
    @Test
    public void test1() throws IOException {
        String filepath="C:\\a.xls";
        FileInputStream fs=new FileInputStream(filepath);
        Workbook workbook = getWorkbook(Excel2003, fs);

        List<String> list=new ArrayList<String>();
        list.add("a");
        list.add("b");
        list.add("c");

        List<List<String>> lt=new ArrayList<List<String>>();
        lt.add(list);
        lt.add(list);
        lt.add(list);

//        String sheetName="工作表";
//        Workbook wk=new HSSFWorkbook();
        Workbook wk=createExcel(workbook,workbook.getSheetName(0),lt);
//        Map<int[],String> valueMap1 = new HashMap<int[], String>();
//        int[]a= {0,0};
//        valueMap1.put(a, "abcdefg");
//        Workbook wk=insertExcel(workbook,0,valueMap1);
        String file="C:\\c.xls";
        FileOutputStream out=new FileOutputStream(file);
        wk.write(out);
        out.flush();

    }
}
